﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class ProfitCentreSQL
    {
        public static string MaxProfitCentreID()
        {
            return @"select MAX(profit_centre_id) from i_profitcentre_mapping";
        }
        public static string InsertProfitCentreMappingAgr()
        {
            return @"
            INSERT INTO [i_profitcentre_mapping]
           ([co_type]
           ,[short_code]
           ,[short_name]
           ,[full_name]
           ,[full_address]
           ,[city]
           ,[country]
           ,[account_code]
           ,[active]
           ,[agr_description]
           ,[agr_dim_value]
           ,[agr_rel_value]
           ,[created_date]
           ,[modified_date]
            ,client)
     VALUES
           (@co_type
           ,@short_code
           ,@short_name
           ,@full_name
           ,@full_address
           ,@city
           ,@country
           ,@account_code
           ,@active
           ,@agr_description
           ,@agr_dim_value
           ,@agr_rel_value
           ,@created_date
           ,@modified_date
            ,@client
            )";
        }
        public static string InsertProfitCentreMapping()
        {
            return @"
            INSERT INTO [i_profitcentre_mapping]
           ([co_type]
           ,[short_code]
           ,[short_name]
           ,[full_name]
           ,[full_address]
           ,[city]
           ,[country]
           ,[account_code]
           ,[active]
           ,[created_date]
           ,[modified_date]
            ,client)
     VALUES
           (@co_type
           ,@short_code
           ,@short_name
           ,@full_name
           ,@full_address
           ,@city
           ,@country
           ,@account_code
           ,@active
           ,@created_date
           ,@modified_date
            ,@client
            )";
        }
        public static string UpdateProfitCentreMapping()
        {
            return @"
           UPDATE [i_profitcentre_mapping] SET
           [co_type] = @co_type
           ,[short_code] = @short_code
           ,[short_name] = @short_name
           ,[full_name] = @full_name
           ,[full_address] = @full_address
           ,[city] = @city
           ,[country] = @country
           ,[account_code] = @account_code
           ,[active] = @active
           ,[agr_description] = @agr_description
           ,[agr_dim_value] = @agr_dim_value
           ,[agr_rel_value] = @agr_rel_value
           ,[modified_date] = @modified_date
            ,client = @client
            ,ccc_dim_value=@ccc_dim_value
            ,expense_account=@expense_account
            ,legal_entity_id = @legal_entity_id
             WHERE profit_centre_id = @profit_centre_id";
        }
        public static string UpdateProfitCentreAgressoMapping()
        {
            return @"
           UPDATE [i_profitcentre_mapping] SET
            [agr_description] = @agr_description
           ,[agr_dim_value] = @agr_dim_value
           ,[agr_rel_value] = @agr_rel_value
           ,[modified_date] = @modified_date
            ,client = @client
             WHERE profit_centre_id = @profit_centre_id";
        }
        public static string GetProfitCentreMappingByName()
        {
            return @"select * from i_profitcentre_mapping where full_name=@full_name ";
        }
        public static string GetProfitCentreMappingByShortName()
        {
            return @"select * from i_profitcentre_mapping where short_name=@short_name ";
        }
        public static string GetProfitCentreMappingByDescription()
        {
            return @"select * from i_profitcentre_mapping where agr_description=@agr_description ";
        }
        public static string GetAllProfitCentreMapping()
        {
            return @"select * from i_profitcentre_mapping order by agr_description";
        }
        public static string GetAllProfitCentreMappingByID()
        {
            return @"select * from i_profitcentre_mapping WHERE profit_centre_id = @profit_centre_id order by full_name";
        }
        public static string GetAllProfitCentreMappingByDimValue()
        {
            return @"select * from i_profitcentre_mapping WHERE agr_dim_value = @agr_dim_value order by full_name";
        }
        public static string GetProfitCentreByFullName()
        {
            return @"select * from i_profitcentre_mapping WHERE full_name = @full_name order by full_name";
        }
        public static string GetAllProfitCentreMapping(string p)
        {
            return @"select * from i_profitcentre_mapping 
                WHERE 
    ([co_type]+[short_code]+[short_name]+[full_name]+[full_address]+[city]+[country]+[account_code]+[active]) like '%"+p+@"%'
order by full_name";
        }
        public static string GetAllSoftmarProfitCentre()
        {
            return @"
                Select distinct vOrgCoTypeSrch.CoType co_type,
                vOrganisation.Code short_code,
                vOrganisation.Name short_name,
                vOrganisation.FullName full_name,
                vCODIRAddressSrch.Address full_address,
                vOrganisation.City city,
                vOrganisation.Country country,
                vOrganisation.SLCode account_code,
                --vOrgUpdated.EnteredBy Entered_By,
                --vOrgUpdated.EnteredAt Entered_Date,
                vOrganisation.Active active
                 FROM VORGANISATION JOIN VCODIRADDRESSSRCH ON VORGANISATION.ID = VCODIRADDRESSSRCH.ORGANISATION 
                 JOIN VORGCOTYPESRCH ON VORGANISATION.ID=VORGCOTYPESRCH.ORGANISATION 
                 LEFT OUTER JOIN VORGUPDATED ON VORGANISATION.ID = VORGUPDATED.ORGANISATION  
                 WHERE (vOrgCoTypeSrch.CoType = 'Profit Centre') AND 
                 (vOrganisation.FullName is NOT NULL) AND 
                 (vOrganisation.Active = 1) 
                 ORDER BY vOrganisation.FullName";
        }
        public static string GetSoftmarProfitCentreByName()
        {
            return @"Select distinct vOrgCoTypeSrch.CoType co_type,
                    vOrganisation.Code short_code,
                    vOrganisation.Name short_name,
                    vOrganisation.FullName full_name,
                    vCODIRAddressSrch.Address full_address,
                    vOrganisation.City city,
                    vOrganisation.Country country,
                    vOrganisation.SLCode account_code,
                    --vOrgUpdated.EnteredBy Entered_By,
                    --vOrgUpdated.EnteredAt Entered_Date,
                    vOrganisation.Active active
                     FROM VORGANISATION JOIN VCODIRADDRESSSRCH ON VORGANISATION.ID = VCODIRADDRESSSRCH.ORGANISATION 
                     JOIN VORGCOTYPESRCH ON VORGANISATION.ID=VORGCOTYPESRCH.ORGANISATION 
                     LEFT OUTER JOIN VORGUPDATED ON VORGANISATION.ID = VORGUPDATED.ORGANISATION  
                     WHERE (vOrgCoTypeSrch.CoType = 'Profit Centre') AND 
                     (vOrganisation.FullName is NOT NULL) AND 
                     (vOrganisation.Active = 1) 
                     AND vOrganisation.FullName = @full_name";
        }
        public static string GetSoftmarProfitCentreByShortName()
        {
            return @"Select distinct vOrgCoTypeSrch.CoType co_type,
                    vOrganisation.Code short_code,
                    vOrganisation.Name short_name,
                    vOrganisation.FullName full_name,
                    vCODIRAddressSrch.Address full_address,
                    vOrganisation.City city,
                    vOrganisation.Country country,
                    vOrganisation.SLCode account_code,
                    --vOrgUpdated.EnteredBy Entered_By,
                    --vOrgUpdated.EnteredAt Entered_Date,
                    vOrganisation.Active active
                     FROM VORGANISATION JOIN VCODIRADDRESSSRCH ON VORGANISATION.ID = VCODIRADDRESSSRCH.ORGANISATION 
                     JOIN VORGCOTYPESRCH ON VORGANISATION.ID=VORGCOTYPESRCH.ORGANISATION 
                     LEFT OUTER JOIN VORGUPDATED ON VORGANISATION.ID = VORGUPDATED.ORGANISATION  
                     WHERE (vOrgCoTypeSrch.CoType = 'Profit Centre') AND 
                     (vOrganisation.FullName is NOT NULL) AND 
                     (vOrganisation.Active = 1) 
                     AND vOrganisation.Name = @full_name";
        }
    }
}
